﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Xml;

namespace PADWH35
{
    public static class getData
    {
        public static DataTable getHeadData()
        {
            string dept= GetConfig("dept_");

            //获取当天领料单
            string sql = $@" SELECT UD110.Key1 [领料单],
                        key2,
           Company [公司],
        ShortChar02 [部门],
        UD110.ShortChar03 [制单人] ,
        UD110.Character02 [备注] ,
        UD110.Date01 [单据日期] ,
        CASE UD110.CheckBox01 WHEN 1 THEN N'正常领料' ELSE N'补料' END [领料类型]--1正常领料、else补料
        FROM Ice.UD110 WHERE  Company = 'NSZ'
        AND ShortChar02 LIKE N'%{dept}%' 
        AND UD110.Date01 = '{DateTime.Now.ToString("yyyy-MM-dd")}'    
 ORDER BY UD110.Key1 DESC;";
            return DBHelper.ExecuteDt(sql);
        }
        public static DataTable getDetailPart(string key1, string key2)
        {
            string sql = $@"SELECT UD110A.ShortChar09 [物料] ,
       UD110A.ShortChar08[物料序号] ,
       Part.PartDescription[描述],          
       ChildKey2[半成品序号] ,
        UD110A.Key1[领料单号] ,
        ChildKey1[工单] ,
        JobHead.PartNum[成品 / 半成品],
        cast(UD110A.Number02 as decimal(18,2)) [工单数量],
        cast(UD110A.Number03 as decimal(18,2))[申请数量] ,          
        (SELECT    cast(SUM(OnhandQty) as decimal(18,2)) 
          FROM Erp.PartBin WITH(NOLOCK )
          WHERE Company = UD110A.Company
                    AND PartNum = UD110A.ShortChar09
                    AND WarehouseCode = UD110A.ShortChar02
        ) [库存数],
        UD110A.ShortChar01[单位],
        UD110A.Date01[需求日期], 
        UD110.Character02[备注]
 FROM   Ice.UD110A WITH(NOLOCK )
        JOIN Ice.UD110 ON UD110.Company = UD110A.Company
                          AND UD110.Key1 = UD110A.Key1
                          AND UD110.Key2 = UD110A.Key2
        LEFT OUTER JOIN Part WITH (NOLOCK ) ON Part.Company = UD110A.Company
                                                AND Part.PartNum = UD110A.ShortChar09
        LEFT OUTER JOIN JobHead ON UD110A.Company = JobHead.Company
                                   AND UD110A.ChildKey1 = JobHead.JobNum
 WHERE UD110A.Company = 'NSZ'
       AND UD110.Key1= '{key1}'
        AND UD110.Key2= '{key2}'
        AND UD110.Date01 >= '{DateTime.Now.ToString("yyyy-MM-dd")}'
            AND UD110.Date01 <= '{DateTime.Now.ToString("yyyy-MM-dd")}'
 ORDER BY UD110A.ShortChar09";
            return DBHelper.ExecuteDt(sql);
        }
        public static string getTranQty(string jobNum, int assem, int jobseq, string tranNo, string PartNum)
        {
            string sql = $@"  
   SELECT   cast(ISNULL(SUM(TranQty), 0) as decimal(18,2)) 
   FROM     PartTran WITH ( NOLOCK )
   WHERE    Company = 'NSZ'
            AND JobNum = '{jobNum}'
            AND AssemblySeq = {assem}
            AND JobSeq = {jobseq}
            AND TranReference LIKE N'%{tranNo}%'
            AND TranQty > 0";
            if (jobNum.Trim() == "")
            { sql += $"  and TranType='STK-UKN' AND PartNum='{PartNum}'"; }
            else { sql += "  and TranType='STK-MTL'"; }
            DataTable result = DBHelper.ExecuteDt(sql);
            if (result.Rows.Count > 0)
            {
                return result.Rows[0][0].ToString();
            }
            else
            {
                return Convert.ToString(0);
            }
        }
        public static  Dictionary<string, DataTable> getDetail(DataTable dt)
        {
            Dictionary<string, DataTable> DetailPart = new Dictionary<string, DataTable>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataTable dtDetail = getDetailPart(dt.Rows[i]["领料单"].ToString(), dt.Rows[i]["key2"].ToString());
                    if (dtDetail.Rows.Count > 0)
                    {
                        dtDetail.Columns.Add(new DataColumn() { ColumnName = "发料数量", DataType = typeof(String) });
                        dtDetail.Columns.Add(new DataColumn() { ColumnName = "是否有交易", DataType = typeof(String) });
                        dtDetail.Columns["发料数量"].SetOrdinal(9);
                        dtDetail.Columns["是否有交易"].SetOrdinal(0);
                        for (int j = 0; j < dtDetail.Rows.Count; j++)
                        {
                            string jobNum = "";
                            int assem = 0;
                            int jobSeq = 0;
                            string tranNo = "";
                            string partNum = "";
                            if (dtDetail.Rows[j]["工单"] != null && dtDetail.Rows[j]["工单"] != DBNull.Value)
                            {
                                jobNum = dtDetail.Rows[j]["工单"].ToString();
                            }
                            if (dtDetail.Rows[j]["半成品序号"].ToString() != "")
                            {
                                assem = Convert.ToInt32(dtDetail.Rows[j]["半成品序号"].ToString());
                            }
                            if (dtDetail.Rows[j]["物料序号"].ToString() != "")//dtDetail.Rows[j]["物料序号"]!=null&& dtDetail.Rows[j]["物料序号"]!=DBNull.Value)
                            {
                                jobSeq = Convert.ToInt32(dtDetail.Rows[j]["物料序号"].ToString());
                            }
                            if (dtDetail.Rows[j]["领料单号"] != null && dtDetail.Rows[j]["领料单号"] != DBNull.Value)
                            {
                                tranNo = dtDetail.Rows[j]["领料单号"].ToString();
                            }
                            if (dtDetail.Rows[j]["物料"] != null && dtDetail.Rows[j]["物料"] != DBNull.Value)
                            {
                                partNum = dtDetail.Rows[j]["物料"].ToString();
                            }
                            string result = getTranQty(jobNum, assem, jobSeq, tranNo, partNum);
                            dtDetail.Rows[j]["发料数量"] = result;
                            if (float.Parse(result) > 0)
                            {
                                dtDetail.Rows[j]["是否有交易"] = "Yes";
                            }
                            else
                            {
                                dtDetail.Rows[j]["是否有交易"] = "No";
                            }
                        }
                        DetailPart.Add(dt.Rows[i]["领料单"].ToString(), dtDetail);
                    }                 
                }       
            }
            return DetailPart;
        }
        public static string GetConfig(string name)
        {
            string value = null;
            XmlDocument doc = new XmlDocument();
            //获得配置文件的全路径  
            string strFileName = AppDomain.CurrentDomain.SetupInformation.ConfigurationFile;//AppDomain.CurrentDomain.BaseDirectory.ToString() + ".exe.config";
            doc.Load(strFileName);
            //找出名称为“add”的所有元素  
            XmlNodeList nodes = doc.GetElementsByTagName("add");
            for (int i = 0; i < nodes.Count; i++)
            {
                //获得将当前元素的key属性  
                XmlAttribute att = nodes[i].Attributes["name"];
                //根据元素的第一个属性来判断当前的元素是不是目标元素  
                if (att.Value == name)
                {
                    //对目标元素中的第二个属性赋值  
                    att = nodes[i].Attributes["connectionString"];
                    value = att.Value;
                    break;
                }
            }
            //保存上面的修改  
            return value;
        }
    }
}
